*****************************************************************
* Replication directory for                                   ***
* Prime locations                                             ***
* by Gabriel M. Ahlfeldt, Thilo N.H. Albers, Kristian Behrens ***
* Published in American Economic Review: Insights             ***
*****************************************************************
* 01/2025
* Stata
version 17.0

* This do file prepares the grid-level data set generated in C++ for Global Cities
	* It insheets the results from the clustering algorithm
	* Merges information from the original grids gnerated in GIS


 * Generate folder for final storage of this do file
	capture mkdir "$temp/grid125"

* Loop over all Global cities
	foreach num of numlist 1/129 {
		qui import delimited "$data_125cities/CLUSTERING/cells/gridcells_`num'_2_output.txt", clear
		display "...metro_id `num'"
	* Process variables
				qui drop v1
				qui drop v2
				qui drop v5 v3 v4
				qui foreach var of varlist v6 v7 v8 v9 {
					replace `var' = `var'*180/_pi
				}
				qui ren  v6 lon_UL
				qui ren  v8 lon_LR
				qui ren  v7 lat_UL
				qui ren  v9 lat_LR
				qui drop v10
				qui ren v11 cell_MFG_emp 	// emp_mfg_wholesale
				qui ren v12 cell_NT_emp 	// emp_nontradable_services
				qui ren v13 cell_PS_emp 	//emp_public_services
				qui ren v14 cell_TS_emp 	// emp_tradable_services
				qui ren v15 cell_O_emp 		// emp_others
				qui ren v16 cell_ST_emp 	// cell_emp_search_terms
				qui ren v17 cell_total_emp
				qui drop v18
				qui ren v19 clusterID
				qui drop v20 
				qui ren v21 cell_id
				qui drop v22 												// dropping developable here since it will be merged from the grid data later on 
			* Generate cbasfp ID
				qui gen metro_id = `num'
				qui split cell_id , p(_)
				qui destring cell_id1 , replace
				qui gen double grid_x = cell_id1
				qui destring cell_id2 , replace
				qui gen double grid_y = cell_id2
				qui  drop cell_id1 cell_id2
				qui gen developable = v23 == .
				qui drop v23
			qui save "$temp/grid125/gridcells_`num'_2_output", replace
	}
* Now append
	clear
	forval num = 1/129 {
	qui append using "$temp/grid125/gridcells_`num'_2_output"
	}
* Generate unique numeric cell
	egen square_id = group(metro_id cell_id)
	qui gen lat = (lat_LR + lat_UL)/2
	qui gen lon = (lon_LR + lon_UL)/2
* Save output file 
	save "$temp/metro_emp_cluster.dta", replace

* Script ends